﻿--获取公司员工档案
CREATE PROCEDURE [dbo].[proc_Employee_GetCompanyList_Excel]
(
	@ygbh nvarchar(50),
	@ygxm nvarchar(50),
	@ygxb nvarchar(10),
	@hyzk nvarchar(10),
	@ygxl nvarchar(10),
	@hk nvarchar(50),
	@dw nvarchar(50),
	@bm varchar(8000),
	@zwjb nvarchar(4), 
	@gwjb nvarchar(4), 
	@rz nvarchar(50), 
	@rzt nvarchar(50),
	@sr nvarchar(50), 
	@lz nvarchar(50), 
	@zz nvarchar(50)	
)	
as	
DECLARE @sqltj VARCHAR(MAX)--查询条件
set @sqltj=''
if(@ygbh<>'')
set @sqltj=@sqltj+' Enumber like '''+@ygbh+''' and'
if(@ygxm<>'')
set @sqltj=@sqltj+' EName like ''%'+@ygxm+'%'' and'
if(@ygxb<>'' and @ygxb<>'0')
set @sqltj=@sqltj+' Sex like '''+@ygxb+''' and'
if(@hyzk<>'' and @hyzk<>'0')
set @sqltj=@sqltj+' Marriage like '''+@hyzk+''' and'
if(@ygxl<>'' and @ygxl<>'0')
set @sqltj=@sqltj+' Education like '''+@ygxl+''' and'
if(@bm<>'' and @bm<>'0')
set @sqltj=@sqltj+' departmentid in ('+@bm+') and'
if(@hk<>'')
set @sqltj=@sqltj+' Cuenta like ''%'+@hk+'%'' and'
if(@dw<>'' and @dw<>'0')
set @sqltj=@sqltj+' CompanyId like '''+@dw+''' and'
if(@zwjb<>'' and @zwjb<>'0')
set @sqltj=@sqltj+' GradeId like '''+@zwjb+''' and'
if(@gwjb<>'' and @gwjb<>'0')
set @sqltj=@sqltj+' PostId like '''+@gwjb+''' and'
if(@rz<>'')
set @sqltj=@sqltj+' RuzhiDate < '''+@rzt+''' and RuzhiDate > '''+@rz+''' and'
if(@zz<>'')
set @sqltj=@sqltj+' ZzDate < '''+@zz+''' and'
if(@sr<>'')
set @sqltj=@sqltj+' Birthday < '''+@sr+''' and'
if(@lz<>'')
set @sqltj=@sqltj+' LizhiDate < '''+@lz+''''
else set @sqltj=@sqltj+' State<>5'
	
	declare @sql3 nvarchar (4000)
	begin
	set @sql3 = 
		+ ' declare @indextable table(id int identity(1,1),nid int)'
		+ ' insert into @indextable (nid)'
		+ ' select id '
		+ ' from employee'
				+ ' where '  
				+@sqltj
		+' order by  enumber asc'

		+' select' 
		+' E.id'
		+' ,E.UserName'
		+' ,E.EName'
		+' ,E.Enumber'
		+' ,E.ETempNumber'
		+' ,E.Sex'
		+' ,E.RuzhiDate'
		+' ,E.CompanyId'
		+' ,(select companyname from company where id=E.CompanyId) as CompanyName'
		+' ,E.DepartmentId'
		+' ,(select dname from Department where id=E.DepartmentId) as DepartmentName'
		+' ,E.ZzDate'
		+' ,E.PostId'
		+' ,(select PostName from Post where id=E.PostId) as PostName'
		+' ,E.GradeId'
		+' ,(select Title from Grade where id=E.GradeId) as GradeName'		
 		+' ,(select count(0) from @indextable) as coun'
 		+' ,E.Birthday'
 		+' ,E.Marriage'
 		+' ,E.PoliticsStatus'
 		+' ,(select Title from Employee_Education where id=E.Education) as Education'
 		+' ,(select top(1) School from Employee_EducationResume where EId=E.id order by EndDate desc) as Employee_EducationResume'
 		+' ,E.IDcard'
 		+' ,E.MobilePhone'
 		+' ,E.HomePhone'
 		+' ,E.Email'
 		+' ,E.Address'
 		+' ,E.WageCardNo'
 		+' ,E.Cuenta'
 		+' ,(isnull((select Wage from Grade where id=E.GradeId),0)) as BasicWage'
 		+' ,(isnull((select p.PostWage from Post p where p.id=E.PostId),0)) as PostWage'
 		+' ,(datediff(mm,ISNULL(e.RuzhiDate,GetDate()),GetDate())/12*100) as WorkWage'
 		+' ,(isnull((select JxWage from Post where id=E.PostId),0)) as JxWage'
 		+' ,(isnull((select ManQin from Post where id=E.PostId),0)) as ManQin'
 		+' ,IsNull((Select Sum(FA.Amount) From Employee_FixedAllowance FA Where FA.UserName = E.UserName),0) as FAllowance'
 		+' ,ISNULL((Select Sum(EA.Amount) From Employee_Allowance EA Where PostId=E.PostId and EA.Pid=E.Pid and EA.Cid=E.Cid),0) as Allowance'
 				
	+' from employee E'
	+' inner join @indextable t on '
	+' E.id=t.nid'	
		
	exec (@sql3)
	END
	RETURN	